package com.zr.sms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.sms.dao.EmployeeDao;
import com.zr.sms.model.Employee;
import com.zr.sms.util.JDBCUtil;

public class EmployeeDaoImpl implements EmployeeDao{


	public Employee selectEmpByNameAndPsw(String ename, String epsw) {
		Employee   emp  =  new Employee();
		Connection   con =   JDBCUtil.getConnection();
		StringBuffer sql  = new StringBuffer("select * from employee where ename=? and  epsw=?");
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setString(1, ename);
			pst.setString(2, epsw);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				emp.setEid(rs.getInt("eid"));
				emp.setEname(rs.getString("ename"));
				emp.setEpsw(rs.getString("epsw"));
				emp.setEsex(rs.getString("esex"));
				emp.setEbirthday(rs.getString("ebirthday"));
				emp.setEsalary(rs.getInt("esalary"));
				emp.setRid(rs.getInt("rid"));
				emp.setEphone(rs.getString("ephone"));
				emp.setCid(rs.getInt("cid"));
				
			}
		  JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emp;
		
	}


	public int selectRidByEid(int eid) {
		Connection con=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		int rid=0;
		con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select rid from employee where eid=?");
		try {
			stmt=con.prepareStatement(sql.toString());
			stmt.setInt(1, eid);
			rs=stmt.executeQuery();
			if(rs.next()){
				rid=rs.getInt("rid");
			}
	      JDBCUtil.closeJDBC(stmt, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rid;
	
	}



	public int getEmpsConut() {
		int scount = 0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(eid) scount from employee");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scount;
	}



	public List<Employee>  getEmpsByRid(int start, int pageSize) {
		List<Employee> emps = new ArrayList<Employee>(); 
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select eid,ename,esex,ebirthday,ephone,esalary from  employee where rid <> 1");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Employee e = new Employee();
				e.setEid(rs.getInt("eid"));
				e.setEname(rs.getString("ename"));
				e.setEsex(rs.getString("esex"));
				e.setEbirthday(rs.getString("ebirthday"));
				e.setEphone(rs.getString("ephone"));
				e.setEsalary(rs.getInt("esalary"));
				emps.add(e);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emps;
	}

	
	public int addEmpAll(Employee emp) {
		int flag=0;  
		  Connection  con=JDBCUtil.getConnection();
		  String sql="insert into employee (ename,epsw,esex,ebirthday,ephone,esalary,cid,rid) values(?,?,?,?,?,?,?,?)";
		  try {
			  PreparedStatement	pst=con.prepareStatement(sql);
			pst.setString(1, emp.getEname());
			pst.setString(2, emp.getEpsw());
			pst.setString(3, emp.getEsex());
			pst.setString(4, emp.getEbirthday());
			pst.setString(5, emp.getEphone());
			pst.setInt(6, emp.getEsalary());
			pst.setInt(7, emp.getCid());
			pst.setInt(8, emp.getRid());
		    int a=	pst.executeUpdate();
		    if(a>0){
		    	flag=1;
		    }
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  
		return flag;
	}


	public int deleteEmp(String[] eids) {
		int flag = 0;
		 Connection  con=JDBCUtil.getConnection();
		 StringBuffer sql=new StringBuffer("delete from employee where ");
		for(int i=0;i<eids.length-1;i++){
		 sql.append(" eid=? or ");
		}
		sql.append("eid=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			for(int j=0;j<=eids.length-1;j++){
				pst.setInt(j+1,Integer.parseInt(eids[j]));
			}
			int i = pst.executeUpdate();
			if(i>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  return flag;
	}


	public int addEmp(Employee emp) {
		int flag=0;  
		  Connection  con=JDBCUtil.getConnection();
		  String sql="insert into employee (ename,epsw,esex,ebirthday,ephone,esalary,rid) values(?,?,?,?,?,?,?)";
		  try {
			  PreparedStatement	pst=con.prepareStatement(sql);
			pst.setString(1, emp.getEname());
			pst.setString(2, emp.getEpsw());
			pst.setString(3, emp.getEsex());
			pst.setString(4, emp.getEbirthday());
			pst.setString(5, emp.getEphone());
			pst.setInt(6, emp.getEsalary());
			pst.setInt(7, emp.getRid());
		    int a=	pst.executeUpdate();
		    if(a>0){
		    	flag=1;
		    }
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  
		return flag;
	}

	
	public int updateEmp(Employee emp) {
		int flag = 0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update employee set ename=?,esex=?,ebirthday=?,ephone=?,esalary=? where eid=?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, emp.getEname());
			pst.setString(2, emp.getEsex());
			pst.setString(3, emp.getEbirthday());
			pst.setString(4, emp.getEphone());
			pst.setInt(5, emp.getEsalary());
			pst.setInt(6, emp.getEid());
			int  i  = pst.executeUpdate();
			if(i>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	public int getEmpsConut(String searchname) {
		int  scount =  0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(eid) scount from  employee where ename like ?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scount;
	}

	
	public List<Employee> getEmpsByRid(int start, int pageSize, String searchname) {
		List<Employee>  emps = new ArrayList<Employee>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select eid,ename,esex,ebirthday,ephone,esalary from  employee");
		sql.append(" where ename like ? and  rid <> 1 ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				Employee  emp = new Employee();
				emp.setEid(rs.getInt("eid"));
				emp.setEname(rs.getString("ename"));
				emp.setEsex(rs.getString("esex"));
				emp.setEbirthday(rs.getString("ebirthday"));
				emp.setEphone(rs.getString("ephone"));
				emp.setEsalary(rs.getInt("esalary"));
				emps.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emps;
}

	public boolean changeEpsw(String epsw,int eid) {
		Connection con=null;
		boolean flag=false;
		con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("UPDATE employee SET  epsw=? WHERE eid=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, epsw);
			pst.setInt(2, eid);
			int a=pst.executeUpdate();
			if(a>0){
				flag=true;
			}
			 JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		   return flag;
		
	}

	public List<Employee> getUpEmps(int start, int pageSize) {
		List<Employee> emps = new ArrayList<Employee>(); 
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select eid,ename,rid from  employee where rid<>1");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Employee e = new Employee();
				e.setEid(rs.getInt("eid"));
				e.setEname(rs.getString("ename"));
				e.setRid(rs.getInt("rid"));
				emps.add(e);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emps;
	}

	
	public List<Employee> getUpEmps(int start, int pageSize, String searchname) {
		List<Employee>  emps = new ArrayList<Employee>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select eid,ename,rid from employee");
		sql.append(" where ename like ? and rid<>1");
		sql.append(" limit ?,?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				Employee  emp = new Employee();
				emp.setEid(rs.getInt("eid"));
				emp.setEname(rs.getString("ename"));
				emp.setRid(rs.getInt("rid"));
				emps.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emps;
	}


	public int updateEmpPro(Employee emp) {
		int flag = 0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update employee set rid=? where eid=?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, emp.getRid());
			pst.setInt(2, emp.getEid());
			int  i  = pst.executeUpdate();
			if(i>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}


}
